﻿Public Class perContato
    Inherits perPadrao

#Region "Declaração de variáveis"
    Private objBuscaId As New perProximoId
#End Region

#Region "Propriedades"

    Public ReadOnly Property sqlConsulta(ByVal iIdCliente As Int32) As String
        Get
            Dim sSql As String
            sSql = "Select * From DC_Contatos"
            If (iIdCliente > 0) Then
                sSql &= " Where IDCliente = " & iIdCliente
            End If
            sSql &= " ORDER BY nomeContato"

            Return sSql
        End Get
    End Property

#End Region


#Region "Métodos"

    Public Function inserirContato(ByVal sNomeContato As String, _
                                   ByVal iIDCliente As Int32, _
                                  ByVal iIDDepto As Int32, _
                                  ByVal sTelefone As String, _
                                  ByVal sCelular As String, _
                                  ByVal sFax As String, _
                                  ByVal sEmail As String, _
                                  ByVal bPrincipal As Boolean) As Int32

        Dim iIDContato As Int32
        Dim sSql As String

        Try
            iIDContato = Me.objBuscaId.BuscaID("IDContato", "DC_Contatos")

            sSql = "  INSERT INTO DC_Contatos "
            sSql &= " ( "
            sSql &= "   IDContato, "
            sSql &= "   IDCliente, "
            sSql &= "   NomeContato, "
            sSql &= "   IDDepartamento, "
            sSql &= "   Telefone, "
            sSql &= "   Celular, "
            sSql &= "   Fax, "
            sSql &= "   Email, "
            sSql &= "   Principal"
            sSql &= " ) "
            sSql &= " VALUES "
            sSql &= " ( "
            sSql &= iIDContato & ","
            If (iIDCliente = 0) Then
                sSql &= "null,"
            Else
                sSql &= iIDCliente & ","
            End If

            sSql &= "'" & sNomeContato & "',"

            If (iIDDepto = 0) Then
                sSql &= "null,"
            Else
                sSql &= iIDDepto & ","
            End If

            sSql &= "'" & sTelefone & "',"
            sSql &= "'" & sCelular & "',"
            sSql &= "'" & sFax & "',"
            sSql &= "'" & sEmail & "',"
            sSql &= IIf(bPrincipal, 1, 0)
            sSql &= " ) "

            MyBase.executarAcao(sSql)
            Return iIDContato
        Catch ex As Exception
            Throw New Exception("Exceção no método 'inserirContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Sub alterarContato(ByVal iIDContato As Int32, _
                             ByVal sNomeContato As String, _
                             ByVal iIDCliente As Int32, _
                             ByVal iIDDepto As Int32, _
                             ByVal sTelefone As String, _
                             ByVal sCelular As String, _
                             ByVal sFax As String, _
                             ByVal sEmail As String, _
                             ByVal bPrincipal As Boolean)

        Dim sSql As String

        Try

            sSql = "  UPDATE DC_Contatos SET "
            sSql &= "   IDCliente = " & iIDCliente & ", "

            If (iIDDepto > 0) Then
                sSql &= "   IdDepartamento = " & iIDDepto & ", "
            Else
                sSql &= "   IdDepartamento = null, "
            End If

            sSql &= "   NomeContato = '" & sNomeContato & "',"
            sSql &= "   Telefone = '" & sTelefone & "',"
            sSql &= "   Celular = '" & sCelular & "',"
            sSql &= "   Fax = '" & sFax & "',"
            sSql &= "   Email = '" & sEmail & "',"
            sSql &= "   Principal = " & IIf(bPrincipal, 1, 0)
            sSql &= " WHERE "
            sSql &= "   IDContato = " & iIDContato

            MyBase.executarAcao(sSql)

        Catch ex As Exception
            Throw New Exception("Exceção no método 'alterarContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Sub

    Public Sub excluirContato(ByVal iIDContato As Int32)

        Dim sSql As String

        Try
            sSql = "  DELETE FROM "
            sSql &= "   DC_Contatos "
            sSql &= " WHERE "
            sSql &= "   IDContato = " & iIDContato

            MyBase.executarAcao(sSql)

        Catch ex As Exception
            Throw New Exception("Exceção no método 'excluirContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Sub

    Public Function selecionarContato(ByVal iIDContato As Int32, _
                                      ByVal iIDCliente As Int32, _
                                      ByVal bPrincipal As Boolean) As DataSet
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = " SELECT "
            sSql &= "   DC_Contatos.IdContato, "
            sSql &= "   DC_Contatos.NomeContato, "
            sSql &= "   DC_Departamentos.Descricao as Departamento, "
            sSql &= "   DC_Contatos.IDDepartamento, "
            sSql &= "   DC_Contatos.IDCliente, "
            sSql &= "   DC_Contatos.Telefone, "
            sSql &= "   DC_Contatos.Celular, "
            sSql &= "   DC_Contatos.Fax, "
            sSql &= "   DC_Contatos.Email, "
            sSql &= "   DC_Contatos.Principal "
            sSql &= " FROM DC_Contatos "
            sSql &= " LEFT JOIN DC_Departamentos ON DC_Contatos.IDDepartamento = DC_Departamentos.IdDepartamento "
            sSql &= " WHERE DC_Contatos.IDCliente = " & iIDCliente

            If (iIDContato > 0) Then
                sSql &= "   AND DC_Contatos.IDContato = " & iIDContato
            End If

            If (bPrincipal) Then
                sSql &= "   AND DC_Contatos.Principal = 1"
            End If

            sSql &= "ORDER BY DC_Contatos.Principal desc, DC_Contatos.NomeContato"

            dsDados = MyBase.executarConsulta(sSql, "DC_Contatos")

            Return dsDados

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Function verificarContatoDepartamento(ByVal iIdDepartamento As Int32) As DataTable
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = " SELECT IDDepartamento "
            sSql &= " FROM DC_Contatos "

            If (iIdDepartamento > 0) Then
                sSql &= " WHERE IDDepartamento = " & iIdDepartamento
            End If

            dsDados = MyBase.executarConsulta(sSql, "DC_Contatos")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("DC_Contatos").Rows.Count > 0 Then
                Return dsDados.Tables("DC_Contatos")
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'verificarContatoDepartamento'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function retornaContatoPrincipal(ByVal iIDContato As Int32, ByVal iIDCliente As Int32) As DataRow
        Dim sSql As String
        Dim dsDados As DataSet

        Try
            sSql = "SELECT IDContato, NomeContato" & vbCrLf
            sSql &= "FROM DC_Contatos " & vbCrLf
            sSql &= "WHERE DC_Contatos.Principal = 1"
            sSql &= "  AND DC_Contatos.IDCliente = " & iIDCliente

            dsDados = MyBase.executarConsulta(sSql, "DC_Contatos")

            If (Not dsDados Is Nothing AndAlso dsDados.Tables("DC_Contatos").Rows.Count > 0) Then
                Return dsDados.Tables("DC_Contatos").Rows(0)
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw New Exception("Exceção no método 'retornaContatoPrincipal'." & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Function selecionarCampoContato(ByVal sCampo As String, _
                                          ByVal iIDContato As Int32) As DataTable
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = " SELECT " & sCampo
            sSql &= " FROM DC_Contatos "

            If (iIDContato > 0) Then
                sSql &= " WHERE IDContato = " & iIDContato
            End If

            dsDados = MyBase.executarConsulta(sSql, "DC_Contatos")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("DC_Contatos").Rows.Count > 0 Then
                Return dsDados.Tables("DC_Contatos")
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarCampoContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function desmarcarPrincipal(ByVal iIdCliente As Int32) As Boolean
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = "UPDATE DC_Contatos "
            sSql &= " SET Principal = " & 0
            sSql &= "WHERE IdCliente = " & iIdCliente

            MyBase.executarAcao(sSql)

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarCampoContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
#End Region

End Class
